package web.chitietsp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class connectDBCTSP {
	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;
	/**
	 * Check Driver connect to MySQL
	 */
	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}

	/**
	 * Function used to get the connection to the Database Step 1 - I check my
	 * connection or not!! Step 2 - If not, it will be null and initialization.
	 * Step 3 - Then it return
	 * 
	 * @return Connection
	 */
	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/web";
			// url_sql = "....";
			try {
				this.connect = DriverManager
						.getConnection(url, "root", "12345");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}

	/**
	 * Make a Statement to execute the SQL statement
	 * 
	 * @return Statement
	 */
	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}

	/**
	 * Used to execute the Select statement
	 * 
	 * @param strSQL
	 *            Query VD: Select * from Employee
	 * @return ResultSet
	 */
	// thuc hien cac cau lenh cho data sanpham
		public ArrayList<chitietsp> getAllChitietsps() throws Exception {
			ArrayList<chitietsp> lst = new ArrayList<chitietsp>();
			String strSQL = "select * from chitietsp";
			try {
				rs = getStatement().executeQuery(strSQL);
				while (rs.next()) {
					String msp = rs.getString("masp");
					String mctsp = rs.getString("mactsp");
					String ms = rs.getString("mausac");
					double g = Double.parseDouble(rs.getString("gia"));
					float s = Float.parseFloat(rs.getString("size"));
					String h= rs.getString("hinh");
				
					chitietsp ctsp = new chitietsp(msp, mctsp, ms, g, s, h);
					lst.add(ctsp);
				}
			} catch (Exception e) {
				throw new Exception(e.getMessage() + " Error at : " + strSQL);
			}
			closeConnet();
			return lst;
		}

		public boolean insertNew(chitietsp ctsp) throws Exception {
			String sql = "insert into chitietsp values(?,?,?,?,?,?)";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, ctsp.getMasp());
			pst.setString(2, ctsp.getMactsp());
			pst.setString(2, ctsp.getMausac());
			pst.setDouble(4, ctsp.getGia());
			pst.setFloat(5, ctsp.getSize());
			pst.setString(6, ctsp.getHinh());
			
			return pst.executeUpdate() > 0;

		}

		public boolean deleteCTSP(String masp,String mactsp) throws Exception {
			String sql = "delete from chitietsp where masp=? and mactsp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, masp);
			pst.setString(2, mactsp);
			return pst.executeUpdate() > 0;
		}

		/**
		 * Used to execute the Insert, Update, Delete statement
		 * 
		 * @param strSQL
		 *            Query VD: Insert into TableName values ('??','??')
		 * @return The number of lines affected by the command
		 */
		public int executeUpdate(String strSQL) throws Exception {
			int result = 0;
			try {
				result = getStatement().executeUpdate(strSQL);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage() + " Error at: " + strSQL);
			} finally {
				this.closeConnet();
			}
			return result;
		}

		public chitietsp getChiTietSPByID(String masp,String mactsp) throws Exception {
			String sql = "select * from chitietsp where masp=? and mactsp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, masp);
			pst.setString(2, mactsp);
			ResultSet rs = pst.executeQuery();
			chitietsp ctsp = null;
			if (rs.next()) {
				String msp = rs.getString("masp");
				String mctsp = rs.getString("mactsp");
				String ms = rs.getString("mausac");
				double g = Double.parseDouble(rs.getString("gia"));
				float s = Float.parseFloat(rs.getString("size"));
				String h= rs.getString("hinh");			
				 ctsp = new chitietsp(msp, mctsp, ms, g, s, h);
			}
			return ctsp;
		}

		public boolean updateChiTietSP(String masp ,String mactsp, chitietsp newctsp) throws Exception {
			String sql = "update chitietsp set mausac=?, gia=?,size=?, hinh=? where masp=? and mactsp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			
			pst.setString(1, newctsp.getMausac());
			pst.setDouble(2, newctsp.getGia());
			pst.setFloat(3, newctsp.getSize());
			pst.setString(4, newctsp.getHinh());
			pst.setString(5, newctsp.getMasp());
			pst.setString(6, newctsp.getMactsp());
			return pst.executeUpdate() > 0;
		}

		
		/**
		 * A method to close the connection.
		 * 
		 * @throws SQLException
		 */
		public void closeConnet() throws SQLException {
			if (rs != null && !rs.isClosed())
				rs.close();
			if (stmt != null)
				stmt.close();
			if (connect != null)
				connect.close();
		}
}
